from itertools import groupby

from odoo import api,fields, models
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT
from odoo.exceptions import UserError


class PurchaseOrderReport(models.Model):
    _name = 'purchase.order.report'
    _description = "Purchase Order Report"
    _auto = False

    product_id = fields.Many2one(comodel_name='product.product', string='Product')
    # 物料编码
    product_code = fields.Char(string='Product Code',related='product_id.default_code')
    # 物料名称
    product_name = fields.Char(string='Product Name',related='product_id.name')
    # 供应商
    partner_id = fields.Many2one(comodel_name='res.partner', string='Customer')
    # PO号
    purchase_order_id = fields.Many2one('purchase.order', string='Purchase Order')
    # PO号
    order_line_id = fields.Many2one('purchase.order.line', string='Purchase Order Line')
    # 厂别
    manufacture = fields.Char(string='Manufacture')
    # 采购数量
    product_qty = fields.Float(string='Purchase Quantity',digits=(16,2))
    # 单位
    product_uom = fields.Many2one(comodel_name='uom.uom',string='Product Uom')
    #币别
    currency_id = fields.Many2one(comodel_name='res.currency',string='Currenccy')
    # 单位价格
    price_unit = fields.Float(string='Price Unit',digits=(16,2))
    # 总金额
    amount = fields.Float(string='Amount',digits=(16,2))
    # 税率
    taxes_id = fields.Many2many(comodel_name='account.tax', string="Taxes",related='order_line_id.taxes_id')
    # 入库数量
    qty_received = fields.Float(string='Receive Quantity',digits=(16,2))
    # 待检数量（先取0）
    unchecked_qty = fields.Float(string='Unchecked Quantity',digits=(16,2))
    # 退货数量
    return_qty = fields.Float(string='Return Quantity',digits=(16,2),compute='_compute_return_qty')

    def _compute_return_qty(self):
        for rec in self:
            return_pickings = rec.purchase_order_id.picking_ids.filtered(lambda a: '退回' in a.origin and a.state=='done')
            if return_pickings:
                moves = self.env['stock.move'].search([('picking_id','in',return_pickings.ids),('product_id','=',rec.product_id.id)])
                rec.return_qty = sum(moves.mapped('quantity')) if moves else 0
            else:
                rec.return_qty = 0

    # 报废数量(暂时为0)
    scrap_qty = fields.Float('Scrap Quantity')
    # 下单时间
    order_date = fields.Datetime(string='Order Date')
    # 需求交期
    requirement_date = fields.Date(string='Requirement Date')
    # 回复交期
    date_planned = fields.Date(string='Planned Date')
    # 供应商是否回复
    is_reply = fields.Boolean(string='Reply')
    # 实际交期
    date_done = fields.Datetime('Date of Transfer',compute='_compute_date_done')
    def _compute_date_done(self):
        for rec in self:
            pickings = rec.purchase_order_id.picking_ids.filtered(lambda a: '退回' not in a.origin and a.state=='done')
            product_move = self.env['stock.move'].search([('picking_id', 'in', pickings.ids),
                                                             ('product_id', '=', rec.product_id.id)],order='create_date desc')
            if product_move and rec.order_line_id.qty_received>0:
                rec.date_done = product_move[0].picking_id.date_done
            else:
                rec.date_done = None
    # 备注
    remark = fields.Char(string='Remark')



    @property
    def _table_query(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        where = ''
        if start_date and end_date:
            where = f"  and pol.create_date >= '{start_date}' and pol.create_date <= '{end_date}'"


        select_sql = f"""
            SELECT ROW_NUMBER
	( ) OVER ( ) AS ID,
	pol.product_id,
	po.partner_id,
	po.ID AS purchase_order_id,
	pol.ID AS order_line_id,
	'WTT' AS manufacture,
	pol.product_qty,
	pol.product_uom,
	pol.currency_id,
	pol.price_unit,
	pol.price_total AS amount,  -- 含税总价
	pol.qty_received,
	0 AS unchecked_qty,
	0 AS scrap_qty,
	po.create_date AS order_date,
	prl.requirement_date,
	po.date_planned,
	FALSE AS is_reply,
	pol.remark 
FROM
	purchase_order_line pol
	LEFT JOIN purchase_order po ON po.ID = pol.order_id
	LEFT JOIN purchase_requisition_line prl ON prl.ID = pol.pr_line_id 
	where po.state='purchase'


        """
        if where:
            return select_sql+where
        else:
            return select_sql